package com.lcm.weam.util;

import com.lcm.weam.entity.algo.GMResult;
import com.lcm.weam.entity.algo.MarkovResult;
import com.lcm.weam.entity.algo.SurfaceWaterEva;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 服务于算法algo模块读取excel文件和输出excel文件
 */
public class ExcelHandler {

    private final static String xls = ".xls";
    private final static String xlsx = ".xlsx";

    private static Object getCellValue(Cell cell) {
        //1.获取到单元格的属性类型
        CellType cellType = cell.getCellType();
        //2.根据单元格数据类型获取数据
        Object value = null;
        switch (cellType) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            //NUMERIC是时间和数据类型一起的
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    //日期格式
                    value = cell.getDateCellValue();
                } else {
                    //数字
                    value = cell.getNumericCellValue();
                }
                break;
            case FORMULA: //公式
                value = cell.getCellFormula();
                break;
            default:
                break;
        }
        return value;
    }

    //读取一列数据
    public static double[] readGMData(MultipartFile file) throws IOException {
        String filename = file.getOriginalFilename();
        assert filename != null;
        InputStream is = file.getInputStream();
        Workbook workbook = null;
        if (filename.endsWith(xlsx)){
            workbook = new XSSFWorkbook(is);
        }else if (filename.endsWith(xls)){
            workbook = new HSSFWorkbook(is);
        }else {
            throw new IOException("文件格式错误!");
        }
        Sheet sheet = workbook.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();
        double[] res = new double[rowNum];
        Row row = sheet.getRow(0);
        int cellNum = 0;
        //获取第2行以下的数据
        for (int i = 1; i <= row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (getCellValue(cell) != null){
                cellNum = i;
                break;
            }
        }
        //拿数据(只能获取数值类型数据)
        for (int i = 1; i <= rowNum; i++) {
            res[i-1] = sheet.getRow(i).getCell(cellNum).getNumericCellValue();
        }
        is.close();
        return res;
    }

    //根据gm结果生成excel输出流
    public static ByteArrayOutputStream writeGMResult(GMResult gmResult) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        Double[] data = gmResult.getData();
        String[] verify = gmResult.getVerify();
        Double[] res = gmResult.getRes();
        Double[] relativeError = gmResult.getRelativeError();
        double correlationDegree = gmResult.getCorrelationDegree();
        double c = gmResult.getSmallErrorProbability();
        for (int i = 0; i <= res.length; i++) {
            Row row = sheet.createRow(i);
            if (i == 0){
                row.createCell(2).setCellValue("原始数据");
                row.createCell(3).setCellValue("级比校验");
                row.createCell(4).setCellValue("预测值");
                row.createCell(5).setCellValue("相对误差");
                row.createCell(6).setCellValue("关联度");
                row.createCell(7).setCellValue("后验差比值");
            } else if (i == 1){
                row.createCell(2).setCellValue(data[0]);
                row.createCell(3).setCellValue(verify[0]);
                row.createCell(4).setCellValue(res[0]);
                row.createCell(5).setCellValue(relativeError[i-1]);
                row.createCell(6).setCellValue(correlationDegree);
                row.createCell(7).setCellValue(c);
            }else if (i > 1 && i <= data.length){
                row.createCell(2).setCellValue(data[i-1]);
                if (i <= data.length - 1){
                    row.createCell(3).setCellValue(verify[i-1]);
                }
                row.createCell(4).setCellValue(res[i-1]);
                row.createCell(5).setCellValue(relativeError[i-1]);
            }else{
                row.createCell(4).setCellValue(res[i-1]);
            }
        }
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        return outputStream;
    }

    //根据markov结果生成excel输出流
    public static ByteArrayOutputStream writeMarkovResult(MarkovResult markovResult) throws IOException {
        Double[] data = markovResult.getData(); //原数据
        Double mean = markovResult.getMean(); //原数据均值
        Double s = markovResult.getMeanSquare(); //原数据标准差
        Double[] verify = markovResult.getVerify(); //原数据各阶验证值x方
        Double[] dataStates = markovResult.getDataStates(); //原数据状态
        Integer t = markovResult.getT(); //迭代次数
        Double[] limits = markovResult.getLimits(); //分级
        List<Double[][]> matrices = markovResult.getMatrices(); //转移概率矩阵
        List<Double> values = markovResult.getValues(); //预测值集合

        double max = Arrays.stream(BaseTypeUtil.DoubleArrTodoubleArr(data)).max().getAsDouble();
        double min = Arrays.stream(BaseTypeUtil.DoubleArrTodoubleArr(data)).min().getAsDouble();
        int m = limits.length + 1; //状态数

        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        //在第一行写入表头
        Row head = sheet.createRow(0);
        head.createCell(1).setCellValue("原始数据：");
        head.createCell(2).setCellValue("状态分类：");
        head.createCell(4).setCellValue("分类区间：");
//        head.createCell(6).setCellValue("马氏性验证结果(显著水平a=0.05)");
//        CellRangeAddress region2 = new CellRangeAddress(0, 0, 6, 7);
//        sheet.addMergedRegion(region2);

//        int num4verify = 0;
        int num4values = 0;
        CellStyle backgroundStyle = workbook.createCellStyle();
        backgroundStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        backgroundStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        int time = Math.max(data.length, (15 + m + t));
        //在第二行写入原数据
        for (int i = 1; i <= time; i++) {
            Row row = sheet.createRow(i);
            if (i <= data.length){
                row.createCell(1).setCellValue(data[i-1]);
                row.createCell(2).setCellValue(dataStates[i-1]);
            }
            if (i == 1){
                row.createCell(4).setCellValue("[" + min + ", " + limits[i-1] + "]");
            }else if (i < m){
                row.createCell(4).setCellValue("[" + limits[i-2] + ", " + limits[i-1] + "]");
            }else if (i == m){
                row.createCell(4).setCellValue("[" + limits[i-2] + ", " + max + "]");
            }else if (i == (m + 2)){
                row.createCell(4).setCellValue("均值：");
            }else if (i == (m + 3)){
                row.createCell(4).setCellValue(mean);
            }else if (i == (m + 4)){
                row.createCell(4).setCellValue("均方差：");
            }else if (i == (m + 5)){
                row.createCell(4).setCellValue(s);
            }else if (i == (m + 7)){
                row.createCell(4).setCellValue("卡方分布统计量");
            }else if (i == (m + 8)){
                Cell cell = row.createCell(4);
                cell.setCellValue(verify[0]);
//                cell.setCellValue(verify[num4verify]);
//                num4verify ++;
            }else if (i == (m + 8 + 2)){
                Cell cell = row.createCell(4);
                cell.setCellValue("预测迭代次数：" + t);
                cell.setCellStyle(backgroundStyle);
            }else if(i == (m + 8 + 3)){
                Cell cell = row.createCell(4);
                cell.setCellValue("模型预测结果：");
                cell.setCellStyle(backgroundStyle);
            }else if (i >= (m + 8 + 4) && i < (m + 8 + 4 + t)){
                Cell cell = row.createCell(4);
                cell.setCellValue(values.get(num4values));
                cell.setCellStyle(backgroundStyle);
                num4values ++;
            }
        }

        int step = matrices.size(); //阶数=5
        //一步的转移概率矩阵包括头行标题，数据，和尾部一空行
        for (int i = 0; i < step; i++) {
            Row row = sheet.getRow(1 + i * (m + 2));
            if (row == null){
                row = sheet.createRow(1 + i * (m + 2));
            }
            row.createCell(6).setCellValue(i+1 + "阶转移概率矩阵：");
            for (int j = 0; j < m; j++) {
                Row row4matrix = sheet.getRow(2 + i * (m + 2) + j);
                if (row4matrix == null){
                    row4matrix = sheet.createRow(2 + i * (m + 2) + j);
                }
                for (int k = 0; k < m; k++) {
                    row4matrix.createCell(6 + k).setCellValue(matrices.get(i)[j][k]);
                }
            }
        }

        sheet.setColumnWidth(4,50*256);

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        return outputStream;
    }


    public static List<SurfaceWaterEva> readSurfaceWaterData(MultipartFile file, int baseLevel) throws IOException {
        String filename = file.getOriginalFilename();
        assert filename != null;
        InputStream is = file.getInputStream();
        Workbook workbook = null;
        if (filename.endsWith(xlsx)){
            workbook = new XSSFWorkbook(is);
        }else if (filename.endsWith(xls)){
            workbook = new HSSFWorkbook(is);
        }else {
            throw new IOException("文件格式错误!");
        }
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum == 2){
            return null;
        }
        List<SurfaceWaterEva> evaList = new ArrayList<>();
        for (int i = 3; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            SurfaceWaterEva eva = setSurfaceWaterEva(row);
            eva.setBaseLevel(baseLevel);
            eva.doEva();
            evaList.add(eva);
        }
        return evaList;
    }

    private static SurfaceWaterEva setSurfaceWaterEva(Row row){
        SurfaceWaterEva eva = new SurfaceWaterEva();
        Cell cell = null;
        cell = row.getCell(1);
        if (cell == null){
            eva.setName("未命名");
        }else {
            eva.setName((String)getCellValue(cell));
        }
        cell = row.getCell(2);
        if (cell == null){
            eva.setType("河流型");
        }else {
            String type = (String)getCellValue(cell);
            if (!"河流型".equals(type) && !"湖库型".equals(type)){
                eva.setType("河流型");
            }else {
                eva.setType(type);
            }
        }
        cell = row.getCell(3);
        if (cell != null){
            eva.setTemperature((Double)getCellValue(cell));
        }
        cell = row.getCell(4);
        if (cell != null){
            eva.setPH((Double)getCellValue(cell));
        }
        cell = row.getCell(5);
        if (cell != null){
            eva.setDO((Double)getCellValue(cell));
        }
        cell = row.getCell(6);
        if (cell != null){
            eva.setPermanganateIndex((Double)getCellValue(cell));
        }
        cell = row.getCell(7);
        if (cell != null){
            eva.setCOD((Double)getCellValue(cell));
        }
        cell = row.getCell(8);
        if (cell != null){
            eva.setBOD5((Double)getCellValue(cell));
        }
        cell = row.getCell(9);
        if (cell != null){
            eva.setNH3N((Double)getCellValue(cell));
        }
        cell = row.getCell(10);
        if (cell != null){
            eva.setTP((Double)getCellValue(cell));
        }
        cell = row.getCell(11);
        if (cell != null){
            eva.setTN((Double)getCellValue(cell));
        }
        cell = row.getCell(12);
        if (cell != null){
            eva.setCu((Double)getCellValue(cell));
        }
        cell = row.getCell(13);
        if (cell != null){
            eva.setZn((Double)getCellValue(cell));
        }
        cell = row.getCell(14);
        if (cell != null){
            eva.setF((Double)getCellValue(cell));
        }
        cell = row.getCell(15);
        if (cell != null){
            eva.setSe((Double)getCellValue(cell));
        }
        cell = row.getCell(16);
        if (cell != null){
            eva.setAs((Double)getCellValue(cell));
        }
        cell = row.getCell(17);
        if (cell != null){
            eva.setHg((Double)getCellValue(cell));
        }
        cell = row.getCell(18);
        if (cell != null){
            eva.setCd((Double)getCellValue(cell));
        }
        cell = row.getCell(19);
        if (cell != null){
            eva.setCr((Double)getCellValue(cell));
        }
        cell = row.getCell(20);
        if (cell != null){
            eva.setPb((Double)getCellValue(cell));
        }
        cell = row.getCell(21);
        if (cell != null){
            eva.setCyan((Double)getCellValue(cell));
        }
        cell = row.getCell(22);
        if (cell != null){
            eva.setVolatilePhenol((Double)getCellValue(cell));
        }
        cell = row.getCell(23);
        if (cell != null){
            eva.setPetroleum((Double)getCellValue(cell));
        }
        cell = row.getCell(24);
        if (cell != null){
            eva.setAnionics((Double)getCellValue(cell));
        }
        cell = row.getCell(25);
        if (cell != null){
            eva.setS((Double)getCellValue(cell));
        }
        cell = row.getCell(26);
        if (cell != null){
            eva.setFecalColiform((Double)getCellValue(cell));
        }
        return eva;
    }

    public static ByteArrayOutputStream writeSurfaceWaterEvaResult(List<SurfaceWaterEva> evalist) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet1 = workbook.createSheet();
        for (int i = 0; i <= evalist.size() + 2; i++){
            Row row = sheet1.createRow(i);
            if (i == 0){
                row.createCell(1).setCellValue("名称");
                row.createCell(2).setCellValue("类型");
                row.createCell(3).setCellValue("评级");
                row.createCell(4).setCellValue("指标个数");
                row.createCell(5).setCellValue("单因子指标法");
                row.createCell(6).setCellValue("综合指标法");
            }
            if (i > 0 && i <= evalist.size()){
                SurfaceWaterEva eva = evalist.get(i-1);
                row.createCell(1).setCellValue(eva.getName());
                row.createCell(2).setCellValue(eva.getType());
                row.createCell(3).setCellValue(level2String(eva.getLevel()) + "类");
                row.createCell(4).setCellValue(eva.getSingleValue().size());
                row.createCell(5).setCellValue(eva.getSingleValue().toString());
                row.createCell(6).setCellValue(eva.getMutiValue());
            }
            if (i == evalist.size()+2){
                String cv = "标准级别：" + level2String(evalist.get(0).getBaseLevel()) + "类";
                row.createCell(1).setCellValue(cv);
            }
        }
        Sheet sheet2 = workbook.createSheet();
        for (int i = 0; i <= evalist.size(); i++) {
            Row row = sheet2.createRow(i);
            if (i==0){
                row.createCell(1).setCellValue("名称");
                row.createCell(2).setCellValue("类型");
                row.createCell(3).setCellValue("评级");
                row.createCell(4).setCellValue("水温(℃)");
                row.createCell(5).setCellValue("PH值（无量纲）");
                row.createCell(6).setCellValue("溶解氧");
                row.createCell(7).setCellValue("高锰酸盐指数");
                row.createCell(8).setCellValue("化学需氧量（COD）");
                row.createCell(9).setCellValue("五日生化需氧量（BOD5）");
                row.createCell(10).setCellValue("氨氮（NH3-N）");
                row.createCell(11).setCellValue("总磷（以P计）");
                row.createCell(12).setCellValue("总氮（湖、库，以N计）");
                row.createCell(13).setCellValue("铜");
                row.createCell(14).setCellValue("锌");
                row.createCell(15).setCellValue("氟化物（以Fˉ计）");
                row.createCell(16).setCellValue("硒");
                row.createCell(17).setCellValue("砷");
                row.createCell(18).setCellValue("汞");
                row.createCell(19).setCellValue("镉");
                row.createCell(20).setCellValue("铬（六价）");
                row.createCell(21).setCellValue("铅");
                row.createCell(22).setCellValue("氰化物");
                row.createCell(23).setCellValue("挥发酚");
                row.createCell(24).setCellValue("石油类");
                row.createCell(25).setCellValue("阴离子表面活性剂");
                row.createCell(26).setCellValue("硫化物");
                row.createCell(27).setCellValue("粪大肠菌群（个/L）");
            }
            if (i > 0 && i <= evalist.size()){
                SurfaceWaterEva eva = evalist.get(i-1);
                if (eva.getName() != null){
                    row.createCell(1).setCellValue(eva.getName());
                }
                if (eva.getType() != null){
                    row.createCell(2).setCellValue(eva.getType());
                }
                if (eva.getLevel() != null){
                    row.createCell(3).setCellValue(level2String(eva.getLevel()) + "类");
                }
                if (eva.getTemperature() != null){
                    row.createCell(4).setCellValue(eva.getTemperature());
                }
                if (eva.getPH() != null){
                    row.createCell(5).setCellValue(eva.getPH());
                }
                if (eva.getDO() != null){
                    row.createCell(6).setCellValue(eva.getDO());
                }
                if (eva.getPermanganateIndex() != null){
                    row.createCell(7).setCellValue(eva.getPermanganateIndex());
                }
                if (eva.getCOD() != null){
                    row.createCell(8).setCellValue(eva.getCOD());
                }
                if (eva.getBOD5() != null){
                    row.createCell(9).setCellValue(eva.getBOD5());
                }
                if (eva.getNH3N() != null){
                    row.createCell(10).setCellValue(eva.getNH3N());
                }
                if (eva.getTP() != null){
                    row.createCell(11).setCellValue(eva.getTP());
                }
                if (eva.getTN() != null){
                    row.createCell(12).setCellValue(eva.getTN());
                }
                if (eva.getCu() != null){
                    row.createCell(13).setCellValue(eva.getCu());
                }
                if (eva.getZn() != null){
                    row.createCell(14).setCellValue(eva.getZn());
                }
                if (eva.getF() != null){
                    row.createCell(15).setCellValue(eva.getF());
                }
                if (eva.getSe() != null){
                    row.createCell(16).setCellValue(eva.getSe());
                }
                if (eva.getAs() != null){
                    row.createCell(17).setCellValue(eva.getAs());
                }
                if (eva.getHg() != null){
                    row.createCell(18).setCellValue(eva.getHg());
                }
                if (eva.getCd() != null){
                    row.createCell(19).setCellValue(eva.getCd());
                }
                if (eva.getCr() != null){
                    row.createCell(20).setCellValue(eva.getCr());
                }
                if (eva.getPb() != null){
                    row.createCell(21).setCellValue(eva.getPb());
                }
                if (eva.getCyan() != null){
                    row.createCell(22).setCellValue(eva.getCyan());
                }
                if (eva.getVolatilePhenol() != null){
                    row.createCell(23).setCellValue(eva.getVolatilePhenol());
                }
                if (eva.getPetroleum() != null){
                    row.createCell(24).setCellValue(eva.getPetroleum());
                }
                if (eva.getAnionics() != null){
                    row.createCell(25).setCellValue(eva.getAnionics());
                }
                if (eva.getS() != null){
                    row.createCell(26).setCellValue(eva.getS());
                }
                if (eva.getFecalColiform() != null){
                    row.createCell(27).setCellValue(eva.getFecalColiform());
                }
            }

        }
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        return outputStream;
    }

    public static String level2String(Integer baseLevel){
        if (baseLevel != null){
            switch (baseLevel){
                case 0:
                    return "Ⅰ";
                case 1:
                    return "Ⅱ";
                case 2:
                    return "Ⅲ";
                case 3:
                    return "Ⅳ";
                case 4:
                    return "Ⅴ";
                case 5:
                    return "劣Ⅴ";
            }
        }
        return "Ⅰ";
    }
}
